# Code Sample from the tutorial at https://learncodeshare.net/2015/06/26/insert-crud-using-cx_oracle/
#  section titled "Resetting the data"
# The following resets the data for use with the update section
# For both tables:
#  Table data is removed.
#  The identity column is set to start with the id after the starting data.
#  Using the executemany function an array of starting data is inserted into the table.

import cx_Oracle
import os
connectString = os.getenv('DB_CONNECT') # The environment variable for the connect string: DB_CONNECT=user/password@database
con = cx_Oracle.connect(connectString)
cur = con.cursor()

# Delete rows
statement = 'delete from lcs_pets'
cur.execute(statement)

# Reset Identity Coulmn
statement = 'alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)'
cur.execute(statement)

# Delete rows
statement = 'delete from lcs_people'
cur.execute(statement)

# Reset Identity Coulmn
statement = 'alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)'
cur.execute(statement)

# Insert default rows
rows = [{'id':1, 'name':'Bob', 'age':35, 'notes':'I like dogs'}, {'id':2, 'name':'Kim', 'age':27, 'notes':'I like birds'}]
cur.bindarraysize = 2
cur.executemany("insert into lcs_people(id, name, age, notes) values (:id, :name, :age, :notes)", rows)
con.commit()

# Insert default rows
rows = [{'id':1, 'name':'Duke', 'owner':1, 'type':'dog'}, {'id':2, 'name':'Pepe', 'owner':2, 'type':'bird'}]
cur.bindarraysize = 2
cur.executemany("insert into lcs_pets (id, name, owner, type) values (:id, :name, :owner, :type)", rows)
con.commit()

cur.close()
